using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using Kangaroo.Deferreds;

namespace Kangaroo.DAL.SqlServer
{
    /// <summary>
    ///     The SqlHelper class is intended to encapsulate high performance, scalable best practices for common uses of SqlClient.
    /// </summary>
    public static class SqlHelper
    {
        //Database connection strings
        public static string ConnectionString;

        // Hashtable to store cached parameters
        private static readonly Hashtable _parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        ///     Execute a SqlCommand (that returns no resultset) against the database specified in the connection string using the provided parameters.
        /// </summary>
        /// <remarks>
        ///     e.g.: int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString"> a valid connection string for a SqlConnection </param>
        /// <param name="cmdType"> the CommandType (stored procedure, text, etc.) </param>
        /// <param name="cmdText"> the stored procedure name or T-SQL command </param>
        /// <param name="commandParameters"> an array of SqlParamters used to execute the command </param>
        /// <returns> an int representing the number of rows affected by the command </returns>
        public static int ExecuteNonQuery(string connectionString,
            CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            var cmd = new SqlCommand();

            using(var conn = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                var val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        ///     Execute a SqlCommand (that returns no resultset) against an existing database connection using the provided parameters.
        /// </summary>
        /// <remarks>
        ///     e.g.: int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection"> an existing database connection </param>
        /// <param name="cmdType"> the CommandType (stored procedure, text, etc.) </param>
        /// <param name="cmdText"> the stored procedure name or T-SQL command </param>
        /// <param name="commandParameters"> an array of SqlParamters used to execute the command </param>
        /// <returns> an int representing the number of rows affected by the command </returns>
        public static int ExecuteNonQuery(SqlConnection connection,
            CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            var cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            var val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        ///     Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction using the provided parameters.
        /// </summary>
        /// <remarks>
        ///     e.g.: int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans"> an existing sql transaction </param>
        /// <param name="cmdType"> the CommandType (stored procedure, text, etc.) </param>
        /// <param name="cmdText"> the stored procedure name or T-SQL command </param>
        /// <param name="commandParameters"> an array of SqlParamters used to execute the command </param>
        /// <returns> an int representing the number of rows affected by the command </returns>
        public static int ExecuteNonQuery(SqlTransaction trans,
            CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            var cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            var val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        ///     Execute a SqlCommand that returns a resultset against the database specified in the connection string using the provided parameters.
        /// </summary>
        /// <remarks>
        ///     e.g.: SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString"> a valid connection string for a SqlConnection </param>
        /// <param name="cmdType"> the CommandType (stored procedure, text, etc.) </param>
        /// <param name="cmdText"> the stored procedure name or T-SQL command </param>
        /// <param name="commandParameters"> an array of SqlParamters used to execute the command </param>
        /// <returns> A SqlDataReader containing the results </returns>
        public static SqlDataReader ExecuteReader(string connectionString,
            CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            var cmd = new SqlCommand();
            var conn = new SqlConnection(connectionString);

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        ///     Execute a SqlCommand that returns a resultset against the database specified in the connection string using the provided parameters.
        /// </summary>
        /// <remarks>
        ///     e.g.: SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString"> a valid connection string for a SqlConnection </param>
        /// <param name="cmdType"> the CommandType (stored procedure, text, etc.) </param>
        /// <param name="cmdText"> the stored procedure name or T-SQL command </param>
        /// <param name="commandParameters"> an array of SqlParamters used to execute the command </param>
        /// <returns> A SqlDataReader containing the results </returns>
        public static SqlDataReader ExecuteReaderForSingleRow(string connectionString,
            CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            var cmd = new SqlCommand();
            var conn = new SqlConnection(connectionString);

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleRow);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public static IDeferred<SqlDataReader> ExecuteReaderAsync(
            string connectionString,
            CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            return DoExecuteReaderAsync(connectionString, cmdType, cmdText, 
                commandParameters, CommandBehavior.CloseConnection);
        }

        public static IDeferred<SqlDataReader> ExecuteReaderForSingleRowAsync(
            string connectionString,
            CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            return DoExecuteReaderAsync(connectionString, cmdType, cmdText, commandParameters, 
                CommandBehavior.CloseConnection | CommandBehavior.SingleRow);
        }

        private static IDeferred<SqlDataReader> DoExecuteReaderAsync(
            string connectionString, CommandType cmdType, string cmdText,
            SqlParameter[] commandParameters, CommandBehavior commandBehavior)
        {
            return Deferred.New<SqlDataReader>(deferred =>
            {
                var cmd = new SqlCommand();
                var conn = new SqlConnection(connectionString);
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    cmd.BeginExecuteReader(delegate(IAsyncResult asyncResult)
                    {
                        try
                        {
                            var dataReader = cmd.EndExecuteReader(asyncResult);
                            deferred.Resolve(dataReader);
                        }
                        catch (Exception exception)
                        {
                            conn.Close();
                            deferred.Reject(exception);
                        }
                    }, null, commandBehavior);
                    cmd.Parameters.Clear();
                }
                catch (Exception exception)
                {
                    conn.Close();
                    deferred.Reject(exception);
                }
            });
        }

        /// <summary>
        ///     Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string using the provided parameters.
        /// </summary>
        /// <remarks>
        ///     e.g.: Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString"> a valid connection string for a SqlConnection </param>
        /// <param name="cmdType"> the CommandType (stored procedure, text, etc.) </param>
        /// <param name="cmdText"> the stored procedure name or T-SQL command </param>
        /// <param name="commandParameters"> an array of SqlParamters used to execute the command </param>
        /// <returns> An object that should be converted to the expected type using Convert.To{Type} </returns>
        public static object ExecuteScalar(string connectionString,
            CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            var cmd = new SqlCommand();

            using(var connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                var val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        ///     Execute a SqlCommand that returns the first column of the first record against an existing database connection using the provided parameters.
        /// </summary>
        /// <remarks>
        ///     e.g.: Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection"> an existing database connection </param>
        /// <param name="cmdType"> the CommandType (stored procedure, text, etc.) </param>
        /// <param name="cmdText"> the stored procedure name or T-SQL command </param>
        /// <param name="commandParameters"> an array of SqlParamters used to execute the command </param>
        /// <returns> An object that should be converted to the expected type using Convert.To{Type} </returns>
        public static object ExecuteScalar(SqlConnection connection,
            CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            var cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            var val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        ///     add parameter array to the cache
        /// </summary>
        /// <param name="cacheKey"> Key to the parameter cache </param>
        /// <param name="commandParameters"> an array of SqlParamters to be cached </param>
        public static void CacheParameters(string cacheKey,
            params SqlParameter[] commandParameters)
        {
            _parmCache[cacheKey] = commandParameters;
        }

        public static void WithConnection(string connectionString, Action<SqlConnection> action)
        {
            using(var connection = new SqlConnection(connectionString))
            {
                action(connection);
            }
        }

        public static T WithConnection<T>(string connectionString, Func<SqlConnection, T> func)
        {
            using(var connection = new SqlConnection(connectionString))
            {
                return func(connection);
            }
        }

        /// <summary>
        ///     Retrieve cached parameters
        /// </summary>
        /// <param name="cacheKey"> key used to lookup parameters </param>
        /// <returns> Cached SqlParamters array </returns>
        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            var cachedParms = (SqlParameter[])_parmCache[cacheKey];

            if(cachedParms == null)
                return null;

            var clonedParms = new SqlParameter[cachedParms.Length];

            for(int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        ///     Determines whether the specified data record has column.
        /// </summary>
        /// <param name="dr"> The data record. </param>
        /// <param name="columnName"> Name of the column. </param>
        /// <returns> <c>true</c> if the specified data reader has the specified column; otherwise, <c>false</c> . </returns>
        public static bool HasColumn(this IDataRecord dr, string columnName)
        {
            for(var i = 0; i < dr.FieldCount; i++)
            {
                if(dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                    return true;
            }
            return false;
        }

        /// <summary>
        ///     Prepare a command for execution
        /// </summary>
        /// <param name="cmd"> SqlCommand object </param>
        /// <param name="conn"> SqlConnection object </param>
        /// <param name="trans"> SqlTransaction object </param>
        /// <param name="cmdType"> Cmd type e.g. stored procedure or text </param>
        /// <param name="cmdText"> Command text, e.g. Select * from Products </param>
        /// <param name="cmdParms"> SqlParameters to use in the command </param>
        private static void PrepareCommand(SqlCommand cmd,
            SqlConnection conn, SqlTransaction trans,
            CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if(conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if(trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if(cmdParms != null)
            {
                foreach(var parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}